/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fh.controller;

import fh.component.DataSource;
import fh.model.Event;
import fh.model.Player;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.swing.DefaultComboBoxModel;

/**
 *
 * @author Bart
 */
public class EventController {

    public boolean insertMasterclass(Event m) {
        try {
            String insertMasterclassQuery = "INSERT INTO `event` (name, min_rating, max_rating, date, type, entry_fee, max_players, location_id, player_id) values('"
                    + m.getName() + "',"
                    + m.getMin_rating() + ", "
                    + m.getMax_rating() + ", '"
                    + m.getDate() + "', '"
                    + m.getType() + "', "
                    + m.getEntry_fee() + ", "
                    + m.getMax_players() + ", "
                    + m.getLocation_id() + ", "
                    + m.getPlayer_id() + ")";
            DataSource.executeUpdateQuery(insertMasterclassQuery);
            return true;
        } catch (Exception e) {

            System.out.println(e);
            return false;
        }
    }

    public boolean insertTourney(Event t) {
        String insertTourneyQuery = "INSERT INTO `event` (name, date, type, table_cap, entry_fee, max_players, location_id) values('"
                + t.getName() + "','"
                + t.getDate() + "', '"
                + t.getType() + "', "
                + t.getTable_cap() + ", "
                + t.getEntry_fee() + ", "
                + t.getMax_players() + ", "
                + t.getLocation_id() + ")";
        System.out.println(insertTourneyQuery);
        DataSource.executeUpdateQuery(insertTourneyQuery);
        return true;
    }

    public DefaultComboBoxModel getTourneysList() {
        try {
            DefaultComboBoxModel events = new DefaultComboBoxModel();

            String query = "SELECT * FROM event WHERE type = 'Toernooi' ORDER BY date desc";

            ResultSet rs = DataSource.executeSelectQuery(query);
            if (rs != null) {
                Event e;
                while (rs.next()) {
                    e = new Event();
                    e.setId(rs.getInt(1));
                    e.setName(rs.getString(2));
                    e.setMin_rating(rs.getInt(3));
                    e.setMax_rating(rs.getInt(4));
                    e.setDate(rs.getDate(5));
                    e.setType(rs.getString(6));
                    e.setTable_cap(rs.getInt(7));
                    e.setEntry_fee(rs.getInt(8));
                    e.setMax_players(rs.getInt(9));
                    e.setLocation_id(rs.getInt(10));
                    e.setPlayer_id(rs.getInt(11));
                    events.addElement(e);
                }

                return events;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return null;
        }
    }

    public DefaultComboBoxModel getCurrentTourneysList() {
        try {
            String date = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
            DefaultComboBoxModel events = new DefaultComboBoxModel();

            String query = "SELECT * FROM event WHERE type = 'Toernooi' AND"
                    + " date >= '"
                    + date + "' "
                    + "ORDER BY date asc";
            ResultSet rs = DataSource.executeSelectQuery(query);
            if (rs != null) {
                Event e;
                while (rs.next()) {
                    e = new Event();
                    e.setId(rs.getInt(1));
                    e.setName(rs.getString(2));
                    e.setMin_rating(rs.getInt(3));
                    e.setMax_rating(rs.getInt(4));
                    e.setDate(rs.getDate(5));
                    e.setType(rs.getString(6));
                    e.setTable_cap(rs.getInt(7));
                    e.setEntry_fee(rs.getInt(8));
                    e.setMax_players(rs.getInt(9));
                    e.setLocation_id(rs.getInt(10));
                    e.setPlayer_id(rs.getInt(11));
                    events.addElement(e);
                }

                return events;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return null;
        }
    }

    public DefaultComboBoxModel getMasterclassesList() {
        try {
            DefaultComboBoxModel events = new DefaultComboBoxModel();

            String query = "SELECT * FROM event WHERE type = 'Masterclass' ORDER BY date desc";

            ResultSet rs = DataSource.executeSelectQuery(query);
            if (rs != null) {
                Event e;
                while (rs.next()) {
                    e = new Event();
                    e.setId(rs.getInt(1));
                    e.setName(rs.getString(2));
                    e.setMin_rating(rs.getInt(3));
                    e.setMax_rating(rs.getInt(4));
                    e.setDate(rs.getDate(5));
                    e.setType(rs.getString(6));
                    e.setTable_cap(rs.getInt(7));
                    e.setEntry_fee(rs.getInt(8));
                    e.setMax_players(rs.getInt(9));
                    e.setLocation_id(rs.getInt(10));
                    e.setPlayer_id(rs.getInt(11));
                    events.addElement(e);
                }

                return events;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return null;
        }
    }

    public int deleteEventById(int id) {

        String query = "DELETE FROM event WHERE id=?";
        System.out.println(query);
        return DataSource.executeDeleteQuery(query, id);
    }

    public boolean updateTourney(Event e) {
        String updateEventQuery = "UPDATE `event` SET "
                + "`name`='" + e.getName() + "', "
                + "`date`='" + e.getDate() + "', "
                + "`table_cap`=" + e.getTable_cap() + ", "
                + "`entry_fee`=" + e.getEntry_fee() + ", "
                + "`max_players`=" + e.getMax_players() + ", "
                + "`location_id`=" + e.getLocation_id()
                + " WHERE `id`=" + e.getId();
        DataSource.executeUpdateQuery(updateEventQuery);
        return true;
    }

    public boolean updateMasterclass(Event e) {
        String updateEventQuery = "UPDATE `event` SET "
                + "`name`='" + e.getName() + "', "
                + "`min_rating`=" + e.getMin_rating() + ", "
                + "`max_rating`=" + e.getMax_rating() + ", "
                + "`date`='" + e.getDate() + "', "
                + "`entry_fee`=" + e.getEntry_fee() + ", "
                + "`max_players`=" + e.getMax_players() + ", "
                + "`location_id`=" + e.getLocation_id() + ", "
                + "`player_id`=" + e.getPlayer_id()
                + " WHERE `id`=" + e.getId();
        DataSource.executeUpdateQuery(updateEventQuery);
        return true;
    }

    public boolean getPayedStatus(int e_id, int p_id) {
        try {
            String query = "SELECT payed FROM player_event WHERE `player_id`=" + p_id + " AND `event_id`=" + e_id;

            ResultSet rs = DataSource.executeSelectQuery(query);
            if (rs != null) {
                boolean payed = false;
                while (rs.next()) {
                    payed = rs.getBoolean(1);
                }
                return payed;
            } else {
                return false;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return false;
        }
    }

    private List<Player> getSignedUpPlayers(Event event) {
        try {
            List<Player> playerList = new ArrayList<Player>();

            String query = "SELECT * FROM player P INNER JOIN player_event PE ON  P.id=PE.player_id WHERE event_id='" + event.getId() + "'";

            ResultSet rs = DataSource.executeSelectQuery(query);
            if (rs != null) {
                Player p;
                while (rs.next()) {
                    p = new Player();
                    p.setId(rs.getInt(1));
                    p.setFirstname(rs.getString(2));
                    p.setLastname(rs.getString(3));
                    p.setAddress(rs.getString(4));
                    p.setZipcode(rs.getString(5));
                    p.setCity(rs.getString(6));
                    p.setPhonenumber(rs.getString(7));
                    p.setEmail(rs.getString(8));
                    p.setRating(rs.getInt(9));
                    p.setMoney_won(rs.getInt(10));
                    p.setVip(rs.getBoolean(11));

                    playerList.add(p);
                }

                return playerList;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return null;
        }
    }

    public boolean updatePayedStatus(int e_id, int p_id, boolean payed) {
        String query = "UPDATE player_event SET `payed`=" + payed + " WHERE `event_id`=" + e_id + " AND `player_id`=" + p_id;

        DataSource.executeUpdateQuery(query);
        return true;
    }

    public int getRegistratedPlayers(int e_id) {
        try {
            String query = "SELECT COUNT(`event_id`) FROM player_event WHERE `event_id` = " + e_id;

            ResultSet rs = DataSource.executeSelectQuery(query);
            if (rs != null) {
                int count = -1;
                while (rs.next()) {
                    count = rs.getInt(1);
                }
                return count;
            } else {
                return -1;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return -1;
        }
    }

    public int getAllReadyRegistered(int e_id, int p_id) {
        try {
            String query = "SELECT player_id FROM player_event WHERE `player_id`=" + p_id + " AND `event_id`=" + e_id;

            ResultSet rs = DataSource.executeSelectQuery(query);
            if (rs != null) {
                int id = -1;
                while (rs.next()) {
                    id = rs.getInt(1);
                }
                return id;
            } else {
                return -1;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return -1;
        }
    }

    public int getCheckVipPlayer(int e_id) {
        try {
            String query = "SELECT player_id FROM event WHERE `id`=" + e_id;

            ResultSet rs = DataSource.executeSelectQuery(query);
            if (rs != null) {
                int id = -1;
                while (rs.next()) {
                    id = rs.getInt(1);
                }
                return id;
            } else {
                return -1;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return -1;
        }
    }
}
